import xlwings as xw
import datetime
import os
import time

app = xw.App(visible=False, add_book=False)
time_address = 'test.xlsx'


# 读取Book
def read_book(book_address):
    book = app.books.open(f"{book_address}")
    return book


# 创建Book
def add_book():
    book = app.books.add()
    return book


# 创建Sheet
def add_sheet(work_book, sheet_name, after_name):
    sheet = work_book.sheets.add(f"{sheet_name}", after=after_name)
    return sheet


def get_row_max(sheet):
    info = sheet.used_range  # 找最后行
    row_max = info.last_cell.row
    print(row_max)
    return row_max


# 写入Sheet
def write_sheet(sheet, sheet_date):
    sheet.range("a1").value = sheet_date


# 切片时转换标准时
def scale_to_time(time_scale):
    seconds = round(time_scale * 24 * 60 * 60, 2)  # 换算总时间(秒)
    m, s = divmod(seconds, 60)  # 计算总分钟(余秒)
    h, m = divmod(m, 60)  # 换算总小时(余分)
    time_str = f'{h}:{m}:{s}'  # 获取打卡时刻
    return time_str


# 标准时转换切片时
def time_to_scale(time_time):
    seconds = round(time_time.hour * 3600 + time_time.minute * 60 + time_time.second)
    scale = seconds / (24 * 60 * 60)
    return scale


book_name = 'test.xlsx'  # 原始数据表文件名
original_address = book_name  # 原始数据地址
original_book = read_book(original_address)  # 实例原始数据簿
original_sheet = original_book.sheets[0]  # 实例原始数据表
sheet_row_max = get_row_max(original_sheet)  # 获取原始数据表最大行
print('数据表总行数', sheet_row_max)  # 确认数据表总行数
original_data = original_sheet.range(f"a2:r{sheet_row_max + 1}").value  # 获取原始数据表数据（尾部附加1行空值）
print('数据总行数', len(original_data))  # 确认数据总行数
print('空行：', original_data[sheet_row_max - 1])  # 确认空值是否存在

# 格式化日期与时间
for i in range(sheet_row_max - 1):
    # 格式化日期
    original_date = original_data[i][10]  # 获取日期(int)
    date_str = original_date.replace('\t', '')  # 删除 tab
    original_data[i][10] = datetime.datetime.strptime(date_str, '%Y-%m-%d')  # 转换日期类型(datetime.datetime)

morning_earliest_on_time = datetime.datetime.strptime('4:00:00', '%H:%M:%S')  # 设定上午上班最早打卡时间
morning_earliest_off_time = datetime.datetime.strptime('11:57:00', '%H:%M:%S')  # 设定上午下班最早打卡时间
morning_late_on_time = datetime.datetime.strptime('8:04:00', '%H:%M:%S')  # 设定上午迟到打卡时间
morning_absenteeism_on_time = datetime.datetime.strptime('8:34:00', '%H:%M:%S')  # 设定上午上班矿工打卡时间
# afternoon_earliest_on_time = datetime.datetime.strptime('4:00:00', '%H:%M:%S')  # 设定下午上班最早打卡时间
afternoon_earliest_off_time = datetime.datetime.strptime('17:27:00', '%H:%M:%S')  # 设定下午下班最早打卡时间
afternoon_late_on_time = datetime.datetime.strptime('14:04:00', '%H:%M:%S')  # 设定下午迟到打卡时间
afternoon_absenteeism_on_time = datetime.datetime.strptime('14:34:00', '%H:%M:%S')  # 设定下午上班矿工打卡时间
row = 0
name = original_data[row][0]  # 获取员工姓名
date = original_data[row][10]  # 获取打卡日期
time = original_data[row][11]  # 获取打卡时间
direction = original_data[row][14]  # 获取打卡方向



if original_data[row + 1][0] == name:
    while True:
        if original_data[row + 1][10] == date:
            if direction == '进':
                if (time - morning_earliest_on_time).days < 0:
                    '无效打卡'
                elif (morning_absenteeism_on_time - time).days < 0:
                    '上午旷工'
                elif (morning_late_on_time - time).days < 0:
                    '上午迟到'

zone = [1, 2]
a = 1.5
if zone[0] <= a <= zone[1]:
    print('ok')
else:
    print('fail')

original_book.close()
exit()
